# _*_ utf-8 _*_
__author__ = "LiuJian"
__time__ = "2021/4/22 0022 13:05"
# @FileName :pyMySql.Set.py

import pymysql

def create_table():
    # 创建数据库连接
    db = pymysql.connect(user='user',
                         password="leboAa!#$123",
                         host='49.233.39.160',
                         database='lebo',
                         port=3306,
                         charset='utf8')
    # 获取游标
    cur = db.cursor()
    # 创建sql

    sql = '''
        create table melon(
            uid int unsigned primary key auto_increment,
            uage int unsigned,
            uname varchar(20),
            uheight decimal(5,2))
    '''
    try:
        cur.execute('drop table if EXISTS melon')
        cur.execute(sql)
        print('建表success')
    except Exception as e:
        print('数据表建失败，原因：%s' % e)
    finally:
        # 关闭对象
        cur.close()
        db.close()

def insert_table():
    db = pymysql.connect(user='user',
                         password="leboAa!#$123",
                         host='49.233.39.160',
                         database='lebo',
                         port=3306,
                         charset='utf8')
    # 获取游标
    cur = db.cursor()
    # 创建sql语句
    sql = '''
        insert into melon (uid, uname, uage, uheight) values
            (1, '王昭君', 21, '180.5'),
            (2, '诸葛亮', 22, '180.6'),
            (3, '张飞', 23, '180.7'),
            (4, '白起', 24, '180.8'),
            (5, '大乔', 25, '180.9'),
            (6, '孙尚香', 26, '180.0'),
            (7, '百里玄策', 27, '180.6'),
            (8, '小乔', 28, null),
            (9, '百里守约', 24, '180.8'),
            (10, '妲己', 26, '180.2'),
            (11, '李白', 28, '180.4'),
            (12, '孙膑', 22, '180.5')       
       '''
    try:
        cur.execute(sql)
        # 提交数据
        db.commit()
        print('数据表插入success')
    except Exception as e:
        print('数据插入失败，原因：%s', e)
        # 回滚数据
        db.rollback()
    finally:
        cur.close()
        db.close()

def check_table():
    db = pymysql.connect(user='user',
                            password="leboAa!#$123",
                            host='49.233.39.160',
                            database='lebo',
                            port=3306,
                            charset="utf8")
    # cur = db.cursor()   # 默认是返回的元组数据类型
    cur = db.cursor(cursor=pymysql.cursors.DictCursor) # cursor=pymysql.cursors.DictCursor：返回的是字典数据类型
    uname = input("请输入姓名：")
    uage = int(input("请输入年龄："))
    sql = '''
        select * from melon where uname = '%s' and uage = %d;
    ''' % (uname, uage)
    print(sql)
    try:
        count = cur.execute(sql)
        ret = cur.fetchall()  # fetchone()  查询一个  fetchmany(2) 查询多个  fetchall() 查询全部
        print(count)
        print(ret)
    except Exception as e:
        print('查询数据失败：%s' % e)
    finally:
        cur.close()
        db.close()

def check_table1():
    db = pymysql.connect(user='user',
                            password="leboAa!#$123",
                            host='49.233.39.160',
                            database='lebo',
                            port=3306,
                            charset="utf8")
    # cur = db.cursor()   # 默认是返回的元组数据类型
    cur = db.cursor(cursor=pymysql.cursors.DictCursor) # cursor=pymysql.cursors.DictCursor：返回的是字典数据类型
    sql = '''
        select * from melon;
    '''
    print(sql)
    try:
        count = cur.execute(sql)
        ret = cur.fetchmany(2)
        print(ret)
        cur.scroll(1, mode='relative')    # 默认：mode="relative" 相对的    absolute 绝对的   4:游标下标值
        ret1 = cur.fetchmany(3)
        print(ret1)

    except Exception as e:
        print('查询数据失败：%s' % e)
    finally:
        cur.close()
        db.close()


# create_table()
# insert_table()
# check_table()
check_table1()

if __name__ == "__main__":
    TheApp = 0
